﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

using System.Data.SqlClient;
using System.Data;
using PRET.ReportDataDefine.UIData;

namespace PRET.DBAdapter.DBAccess
{
    public class AttendanceDB : IDisposable
    {
        private static Dictionary<string, string> TeamsDic = new Dictionary<string, string> {
            {"A班", "16"},
            {"B班", "17"},
            {"C班", ""},
            {"D班", ""},
            {"E班", ""},
            {"F班", ""},
            {"机修班", "21"},
            {"颜色班", "18"},
            {"辅助班", "19"}
        };

        private string ConnString = "";
        //private SqlConnection conn = new SqlConnection();

        #region SQL 语句
        private readonly static string GET_ALLEMPLOYEINFO_BY_DEPID = "SELECT * FROM [STCard_Enp].[dbo].[ST_Person] WHERE [Dept_ID] = '{0}' AND [Card_No] <> '' ORDER BY [Person_ID] ASC";
        private readonly static string GET_EMPLOYEATT_BY_MONTH = "SELECT * FROM [STCard_Enp].[dbo].[KQ_Download] " +
            "WHERE [Brush_Date] LIKE '{0}-%' AND [Person_ID] = '{1}' " +
            "ORDER BY [Brush_DateTime]";
        private readonly static string GET_PERSONID_BY_PERSONNO = "SELECT [Person_ID] FROM [STCard_Enp].[dbo].[ST_Person] " +
            "WHERE [Person_No] = '{0}' AND [Card_No] <> '' GROUP BY [Person_ID]";
        private readonly static string GET_EMPLOYEATT_BY_DAY = "SELECT * FROM [STCard_Enp].[dbo].[KQ_Download] " +
            "WHERE [Person_ID] = '{0}' AND [Brush_Date] >= '{1}' AND [Brush_Date] <= '{2}' " +
            "ORDER BY [Brush_DateTime]";
        #endregion

        public AttendanceDB(string serverAddress, string dbName, string userid, string password)
        {
            this.serverAddress = serverAddress;
            this.dbName = dbName;
            this.userid = userid;
            this.password = password;
            this.ConnString = string.Format("Data Source={0};Initial Catalog={1};User Id={2};Password={3};MultipleActiveResultSets=true;", this.serverAddress, this.dbName, this.userid, this.password);
            //if (IsConnected == false)
            //    ConnectSQL();
        }

        private string serverAddress = "";
        private string dbName = "";
        private string userid = "";
        private string password = "";

        //public void ConnectSQL()
        //{
        //    try
        //    {
        //        conn.ConnectionString = string.Format("Data Source={0};Initial Catalog={1};User Id={2};Password={3};MultipleActiveResultSets=true;", this.serverAddress, this.dbName, this.userid, this.password);
        //        conn.Open();
        //    }
        //    catch (Exception e)
        //    {
        //        string msg = string.Format("AttendanceDB.ConnectSQL() Failed: {0}.\r\nDetail: {1}", e.Message, e.StackTrace);
        //        LogRecord.Error(msg);
        //    }
        //}

        private static object lockObj = new object();
        //public void ReConnectSQL()
        //{
        //    lock (lockObj)
        //    {
        //        if (IsConnected == false)
        //            ConnectSQL();
        //    }
        //}

        //public bool IsConnected
        //{
        //    get
        //    {
        //        if (conn.State == System.Data.ConnectionState.Closed || conn.State == System.Data.ConnectionState.Broken)
        //            return false;
        //        else
        //            return true;
        //    }
        //}

        public void Dispose()
        {
            //try
            //{
            //    if (conn != null && conn.State != System.Data.ConnectionState.Closed)
            //    {
            //        conn.Close();
            //        conn.Dispose();
            //    }
            //}
            //catch (Exception ex)
            //{
            //    string msg = string.Format("AttendanceDB.Dispose() Failed: {0}.\r\nDetail: {1}", ex.Message, ex.StackTrace);
            //    LogRecord.Error(msg);
            //}
        }

        #region 考勤相关
        // 获得一个月内所有班组人员的出勤时间
        public List<Dictionary<string, List<EmployeAttendance>>> GetAllDepEmployeAttendance(DateTime month)
        {
            List<Dictionary<string, List<EmployeAttendance>>> retList = new List<Dictionary<string, List<EmployeAttendance>>>();
            Dictionary<string, List<EmployeAttendance>> dic = new Dictionary<string, List<EmployeAttendance>>();
            if (TeamsDic != null)
            {
                // 获得每一个班组的人员考勤
                foreach(KeyValuePair<string, string> item in TeamsDic)
                {
                    List<EmployeAttendance> list = GetEmployeAttByDep(month, item.Key, item.Value);
                    dic.Add(item.Key, list);
                }
            }
            retList.Add(dic);

            Dictionary<string, List<EmployeAttendance>> dic2 = new Dictionary<string, List<EmployeAttendance>>();
            foreach (KeyValuePair<string, List<EmployeAttendance>> item in dic)
            {
                dic2.Add(item.Key, new List<EmployeAttendance>());
                foreach(EmployeAttendance employe in item.Value)
                {
                    EmployeAttendance obj = EmployeAttendance.CloneSub11(employe);
                    dic2[item.Key].Add(obj);
                }
            }
            retList.Add(dic2);

            return retList;
        }
        private List<EmployeAttendance> GetEmployeAttByDep(DateTime workTime, string DepName, string DepID)
        {
            SqlCommand cmd = null;
            SqlDataReader reader = null;
            try
            {
                //ReConnectSQL();
                List<EmployeAttendance> retList = new List<EmployeAttendance>();

                if (string.IsNullOrEmpty(DepID))
                    return retList;

                // 1. 获得班组内的所有员工编号和名称信息
                string query = string.Format(GET_ALLEMPLOYEINFO_BY_DEPID, DepID);
                SqlConnection conn = new SqlConnection();
                conn.ConnectionString = this.ConnString;
                conn.Open();
                using (cmd = new SqlCommand(query, conn))
                {
                    cmd.CommandTimeout = 0;
                    reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    while(reader.Read())
                    {
                        EmployeAttendance emp = new EmployeAttendance();
                        emp.EmployeID = reader["Person_No"].ToString();
                        emp.Name = reader["Person_Name"].ToString();
                        emp.Year = workTime.Year;
                        emp.Month = workTime.Month;
                        emp.TeamName = DepName;
                        retList.Add(emp);
                    }
                    reader.Close();
                }

                // 2. 遍历员工考勤数据
                string strMonth = workTime.ToString("yyyy-MM");
                for (int i = 0; i < retList.Count; i++ )
                {
                    string personID = "";
                    query = string.Format(GET_PERSONID_BY_PERSONNO, retList[i].EmployeID);
                    conn = new SqlConnection();
                    conn.ConnectionString = this.ConnString;
                    conn.Open();
                    using (cmd = new SqlCommand(query, conn))
                    {
                        cmd.CommandTimeout = 0;
                        reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                        while (reader.Read())
                        {
                            if (!string.IsNullOrEmpty(reader["Person_ID"].ToString()))
                            {
                                personID = reader["Person_ID"].ToString();
                                break;
                            }
                        }
                        reader.Close();
                    }
                    if (string.IsNullOrEmpty(personID))
                        break;
                    query = string.Format(GET_EMPLOYEATT_BY_MONTH, strMonth, personID);
                    conn = new SqlConnection();
                    conn.ConnectionString = this.ConnString;
                    conn.Open();
                    using (cmd = new SqlCommand(query, conn))
                    {
                        DateTime? startTime = null;
                        DateTime? endTime = null;
                        int day = 0;
                        cmd.CommandTimeout = 0;
                        reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                        while(reader.Read())
                        {
                            if (string.IsNullOrEmpty(reader["Brush_DateTime"].ToString()))
                                continue;
                            string mocSign = reader["Moc_No"].ToString();
                            // 上班时间
                            if (mocSign == "01")
                            {
                                startTime = DateTime.Parse(reader["Brush_DateTime"].ToString());
                                day = ((DateTime)startTime).Day;
                            }
                            // 下班时间
                            else if (mocSign == "02" && startTime != null)
                            {
                                endTime = DateTime.Parse(reader["Brush_DateTime"].ToString());
                                TimeSpan span = (TimeSpan)(endTime - startTime);
                                double hours = Math.Round(span.TotalHours, 0);
                                hours = hours < 24 ? hours : 0;
                                switch (day)
                                {
                                    case 0:
                                        break;
                                    case 1:
                                        retList[i].workday1 += hours;
                                        break;
                                    case 2:
                                        retList[i].workday2 += hours;
                                        break;
                                    case 3:
                                        retList[i].workday3 += hours;
                                        break;
                                    case 4:
                                        retList[i].workday4 += hours;
                                        break;
                                    case 5:
                                        retList[i].workday5 += hours;
                                        break;
                                    case 6:
                                        retList[i].workday6 += hours;
                                        break;
                                    case 7:
                                        retList[i].workday7 += hours;
                                        break;
                                    case 8:
                                        retList[i].workday8 += hours;
                                        break;
                                    case 9:
                                        retList[i].workday9 += hours;
                                        break;
                                    case 10:
                                        retList[i].workday10 += hours;
                                        break;
                                    case 11:
                                        retList[i].workday11 += hours;
                                        break;
                                    case 12:
                                        retList[i].workday12 += hours;
                                        break;
                                    case 13:
                                        retList[i].workday13 += hours;
                                        break;
                                    case 14:
                                        retList[i].workday14 += hours;
                                        break;
                                    case 15:
                                        retList[i].workday15 += hours;
                                        break;
                                    case 16:
                                        retList[i].workday16 += hours;
                                        break;
                                    case 17:
                                        retList[i].workday17 += hours;
                                        break;
                                    case 18:
                                        retList[i].workday18 += hours;
                                        break;
                                    case 19:
                                        retList[i].workday19 += hours;
                                        break;
                                    case 20:
                                        retList[i].workday20 += hours;
                                        break;
                                    case 21:
                                        retList[i].workday21 += hours;
                                        break;
                                    case 22:
                                        retList[i].workday22 += hours;
                                        break;
                                    case 23:
                                        retList[i].workday23 += hours;
                                        break;
                                    case 24:
                                        retList[i].workday24 += hours;
                                        break;
                                    case 25:
                                        retList[i].workday25 += hours;
                                        break;
                                    case 26:
                                        retList[i].workday26 += hours;
                                        break;
                                    case 27:
                                        retList[i].workday27 += hours;
                                        break;
                                    case 28:
                                        retList[i].workday28 += hours;
                                        break;
                                    case 29:
                                        retList[i].workday29 += hours;
                                        break;
                                    case 30:
                                        retList[i].workday30 += hours;
                                        break;
                                    case 31:
                                        retList[i].workday31 += hours;
                                        break;
                                }
                                
                                startTime = null;
                                endTime = null;
                                day = 0;
                            }                            
                        }
                        reader.Close();
                    }
                }
                return retList;
            }
            catch (Exception ex)
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();
                //if (cmd != null)
                    //cmd.Dispose();
                string msg = string.Format("AttendanceDB.GetEmployeAttByDep() Failed: {0}.\r\nDetail: {1}", ex.Message, ex.StackTrace);
                LogRecord.Error(msg);
                return null;
            }
        }

        // 获得一个月内所有班组的出勤时间
        public List<TeamAttendance> GetAllTeamAttendance(DateTime month)
        {
            List<TeamAttendance> retList = new List<TeamAttendance>();
            // 按照每天遍历班组工作时间
            for(DateTime startDay = month; startDay < month.AddMonths(1); )
            {
                TeamAttendance team = new TeamAttendance();
                team.Year = startDay.Year;
                team.Month = startDay.Month;
                team.Day = startDay.Day;
                // 取出A班考勤时间
                team.ATeamHours = this.GetTeamHours(startDay, "16");
                // 取出B考勤时间
                team.BTeamHours = this.GetTeamHours(startDay, "17");
                // 取出C班考勤时间
                team.CTeamHours = this.GetTeamHours(startDay, "");
                // 取出D班考勤时间
                team.DTeamHours = this.GetTeamHours(startDay, "");
                // 取出E班考勤时间
                team.ETeamHours = this.GetTeamHours(startDay, "");
                // 取出F班考勤时间
                team.FTeamHours = this.GetTeamHours(startDay, "");
                // 取出颜色班考勤时间
                team.ColorTeamHours = this.GetTeamHours(startDay, "18");
                // 取出机修班考勤时间
                team.JXTeamHours = this.GetTeamHours(startDay, "21");
                // 取出辅助班考勤时间
                team.FZTeamHours = this.GetTeamHours(startDay, "19");
                retList.Add(team);
                startDay = startDay.AddDays(1);
            }
            return retList;
        }
        private double GetTeamHours(DateTime startDay, string DepID)
        {
            SqlCommand cmd = null;
            SqlDataReader reader = null;
            try
            {
                //ReConnectSQL();
                double retHours = 0;
                List<string> idList = new List<string>();
                string query = string.Format(GET_ALLEMPLOYEINFO_BY_DEPID, DepID);
                SqlConnection conn = new SqlConnection();
                conn.ConnectionString = this.ConnString;
                conn.Open();
                using (cmd = new SqlCommand(query,conn))
                {
                    cmd.CommandTimeout = 0;
                    reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    while (reader.Read())
                    {
                        if (!string.IsNullOrEmpty(reader["Person_ID"].ToString()))
                            idList.Add(reader["Person_ID"].ToString());
                    }
                    reader.Close();
                }
                foreach(string id in idList)
                {
                    string sWorkTime = startDay.ToString("yyyy-MM-dd");
                    string eWorkTime = startDay.AddDays(1).ToString("yyyy-MM-dd");
                    query = string.Format(GET_EMPLOYEATT_BY_DAY, id, sWorkTime, eWorkTime);
                    conn = new SqlConnection();
                    conn.ConnectionString = this.ConnString;
                    conn.Open();
                    using (cmd = new SqlCommand(query, conn))
                    {
                        DateTime? startTime = null;
                        DateTime? endTime = null;
                        cmd.CommandTimeout = 0;
                        reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                        while (reader.Read())
                        {
                            if (string.IsNullOrEmpty(reader["Brush_DateTime"].ToString()))
                                continue;
                            string mocSign = reader["Moc_No"].ToString();
                            // 上班时间
                            if (mocSign == "01")
                            {
                                startTime = DateTime.Parse(reader["Brush_DateTime"].ToString());
                                if (((DateTime)startTime).Day != startDay.Day)
                                    startTime = null;
                            }
                            // 下班时间
                            else if (mocSign == "02" && startTime != null)
                            {
                                endTime = DateTime.Parse(reader["Brush_DateTime"].ToString());
                                TimeSpan span = (TimeSpan)(endTime - startTime);
                                double hours = Math.Round(span.TotalHours, 0);
                                if (hours < 24)
                                {
                                    hours = hours > 11 ? 11 : hours;
                                    retHours += hours;
                                }
                                startTime = null;
                                endTime = null;
                            }
                        }
                        reader.Close();
                    }
                }
                return retHours;
            }
            catch (Exception ex)
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();
                //if (cmd != null)
                    //cmd.Dispose();
                string msg = string.Format("AttendanceDB.GetTeamHours() Failed: {0}.\r\nDetail: {1}", ex.Message, ex.StackTrace);
                LogRecord.Error(msg);
                return 0;
            }
        }
        #endregion

        public Dictionary<string, List<double>> GetTeamYearWorkTime(DateTime year)
        {
            Dictionary<string, List<double>> retDic = new Dictionary<string, List<double>>();
            foreach(KeyValuePair<string, string> item in TeamsDic)
            {
                DateTime startTime = year;
                List<double> teamMonthHours = new List<double>();
                while(startTime < year.AddYears(1))
                {
                    double hours = GetTeamMonthWorkTime(item.Value, startTime);
                    teamMonthHours.Add(hours);
                    startTime = startTime.AddMonths(1);
                }
                retDic.Add(item.Key, teamMonthHours);
            }
            return retDic;
        }
        private double GetTeamMonthWorkTime(string DepID, DateTime month)
        {
            SqlCommand cmd = null;
            SqlDataReader reader = null;
            try
            {
                //ReConnectSQL();
                double retHours = 0;
                List<string> idList = new List<string>();
                string query = string.Format(GET_ALLEMPLOYEINFO_BY_DEPID, DepID);
                SqlConnection conn = new SqlConnection();
                conn.ConnectionString = this.ConnString;
                conn.Open();
                using (cmd = new SqlCommand(query, conn))
                {
                    cmd.CommandTimeout = 0;
                    reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    while (reader.Read())
                    {
                        if (!string.IsNullOrEmpty(reader["Person_ID"].ToString()))
                            idList.Add(reader["Person_ID"].ToString());
                    }
                    reader.Close();
                }

                foreach (string id in idList)
                {
                    Dictionary<int, double> workDateDic = new Dictionary<int, double>();
                    string workTime = month.ToString("yyyy-MM");
                    query = string.Format(GET_EMPLOYEATT_BY_MONTH, workTime , id);
                    conn = new SqlConnection();
                    conn.ConnectionString = this.ConnString;
                    conn.Open();
                    using (cmd = new SqlCommand(query, conn))
                    {
                        DateTime? startTime = null;
                        DateTime? endTime = null;
                        int day = 0;
                        cmd.CommandTimeout = 0;
                        reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                        while (reader.Read())
                        {
                            if (string.IsNullOrEmpty(reader["Brush_DateTime"].ToString()))
                                continue;
                            string mocSign = reader["Moc_No"].ToString();
                            // 上班时间
                            if (mocSign == "01")
                            {
                                startTime = DateTime.Parse(reader["Brush_DateTime"].ToString());
                                day = ((DateTime)startTime).Day;
                            }
                            // 下班时间
                            else if (mocSign == "02" && startTime != null)
                            {
                                endTime = DateTime.Parse(reader["Brush_DateTime"].ToString());
                                TimeSpan span = (TimeSpan)(endTime - startTime);
                                double hours = Math.Round(span.TotalHours, 0);
                                if (day != 0)
                                {
                                    if (workDateDic.Keys.Contains(day))
                                    {
                                        workDateDic[day] = workDateDic[day] + hours;
                                        workDateDic[day] = workDateDic[day] > 11 ? 11 : workDateDic[day];
                                    }
                                    else
                                    {
                                        hours = hours > 11 ? 11 : hours;
                                        workDateDic.Add(day, hours);
                                    }
                                }
                                startTime = null;
                                endTime = null;
                                day = 0;
                            }
                        }
                        reader.Close();
                    }
                    foreach(KeyValuePair<int, double> item in workDateDic)
                    {
                        retHours += item.Value;
                    }
                }
                if (retHours > 0)
                    retHours = Math.Round(retHours, 0);
                return retHours;
            }
            catch (Exception ex)
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();
                //if (cmd != null)
                    //cmd.Dispose();
                string msg = string.Format("AttendanceDB.GetTeamMonthWorkTime() Failed: {0}.\r\nDetail: {1}", ex.Message, ex.StackTrace);
                LogRecord.Error(msg);
                return 0;
            }
        }
    }
}
